﻿using System;
using System.Configuration;
using System.Data.SqlClient;
using System.Web.UI;
using Telerik.Web.UI;

public partial class Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            EnsureChartStoredProcedures();
        }
    }

    private static void EnsureChartStoredProcedures()
    {
        var connectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
        var script = @"
            IF OBJECT_ID(N'dbo.ChartOrdersByEmployee', N'P') IS NULL
                EXEC('CREATE PROCEDURE dbo.ChartOrdersByEmployee AS SELECT 1 AS Placeholder');
            ALTER PROCEDURE dbo.ChartOrdersByEmployee
            AS
            BEGIN
                SET NOCOUNT ON;

                SELECT CONCAT(e.FirstName, ' ', e.LastName) AS EmployeeName,
                       COUNT(*) AS TotalOrders
                FROM Orders o
                INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
                GROUP BY e.FirstName, e.LastName
                ORDER BY TotalOrders DESC;
            END;

            IF OBJECT_ID(N'dbo.ChartSalesByCategory', N'P') IS NULL
                EXEC('CREATE PROCEDURE dbo.ChartSalesByCategory AS SELECT 1 AS Placeholder');
            ALTER PROCEDURE dbo.ChartSalesByCategory
            AS
            BEGIN
                SET NOCOUNT ON;

                SELECT c.CategoryName,
                       CAST(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS decimal(18, 2)) AS TotalSales
                FROM [Order Details] od
                INNER JOIN Products p ON p.ProductID = od.ProductID
                INNER JOIN Categories c ON c.CategoryID = p.CategoryID
                GROUP BY c.CategoryName
                ORDER BY TotalSales DESC;
            END;

            IF OBJECT_ID(N'dbo.ChartMonthlyOrders', N'P') IS NULL
                EXEC('CREATE PROCEDURE dbo.ChartMonthlyOrders AS SELECT 1 AS Placeholder');
            ALTER PROCEDURE dbo.ChartMonthlyOrders
            AS
            BEGIN
                SET NOCOUNT ON;

                SELECT CONVERT(varchar(7), OrderDate, 120) AS OrderMonth,
                       COUNT(*) AS TotalOrders
                FROM Orders
                WHERE OrderDate IS NOT NULL
                GROUP BY CONVERT(varchar(7), OrderDate, 120)
                ORDER BY OrderMonth;
            END;";

        using (var connection = new SqlConnection(connectionString))
        using (var command = new SqlCommand(script, connection))
        {
            connection.Open();
            command.ExecuteNonQuery();
        }
    }
}
